package com.healthdata.service;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;

@Service
public class FileParseService {

    public List<Map<String, Object>> parseFile(MultipartFile file, String format) throws IOException {
        // 验证文件是否为空
        if (file == null || file.isEmpty()) {
            throw new IllegalArgumentException("上传的文件为空");
        }
        // 验证文件内容
        byte[] bytes = file.getBytes();
        if (bytes == null || bytes.length == 0) {
            throw new IllegalArgumentException("文件内容为空");
        }

        if ("csv".equalsIgnoreCase(format)) {
            return parseCsv(file);
        } else if ("excel".equalsIgnoreCase(format)) {
            return parseExcel(file);
        } else if ("json".equalsIgnoreCase(format)) {
            return parseJson(file);
        }
        throw new IllegalArgumentException("Unsupported file format: " + format);
    }

    private List<Map<String, Object>> parseCsv(MultipartFile file) throws IOException {
        List<Map<String, Object>> result = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream()))) {
            String line;
            String[] headers = null;
            int rowNum = 0;

            while ((line = reader.readLine()) != null) {
                String[] values = line.split(",");
                if (rowNum == 0) {
                    headers = values;
                    System.out.println("解析到表头: " + Arrays.toString(headers));
                } else {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 0; i < values.length && i < headers.length; i++) {
                        String value = values[i].trim();
                        row.put(headers[i], value.isEmpty() ? null : value);
                    }
                    result.add(row);
                    System.out.println("解析到数据行 " + rowNum + ": " + row);
                }
                rowNum++;
            }
        }
        System.out.println("解析完成，共 " + result.size() + " 行数据");
        return result;
    }

    private List<Map<String, Object>> parseExcel(MultipartFile file) throws IOException {
        List<Map<String, Object>> result = new ArrayList<>();

        try (InputStream is = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {

            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();

            if (!rowIterator.hasNext()) {
                return result;
            }

            // 读取表头
            Row headerRow = rowIterator.next();
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                // 确保所有表头值都是字符串
                cell.setCellType(Cell.CELL_TYPE_STRING);
                headers.add(cell.getStringCellValue());
            }

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                // 跳过空行
                if (isRowEmpty(row)) {
                    continue;
                }

                Map<String, Object> mapRow = new LinkedHashMap<>();

                for (int i = 0; i < headers.size(); i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Object value = getCellValue(cell);
                    mapRow.put(headers.get(i), value);
                }

                result.add(mapRow);
            }
        }
        return result;
    }

    // 检查行是否为空 - POI 3.14 兼容版本
    private boolean isRowEmpty(Row row) {
        if (row == null) return true;

        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    // POI 3.14 兼容版本
    private Object getCellValue(Cell cell) {
        if (cell == null) return null;

        // 在 POI 3.14 中，getCellType() 返回 int 类型
        int cellType = cell.getCellType();

        switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue().trim();
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double numericValue = cell.getNumericCellValue();
                    // 检查是否为整数
                    if (numericValue == Math.floor(numericValue) && !Double.isInfinite(numericValue)) {
                        return String.valueOf((long) numericValue);
                    } else {
                        // 使用 BigDecimal 处理浮点数
                        BigDecimal bigDecimal = BigDecimal.valueOf(numericValue);
                        return bigDecimal.stripTrailingZeros().toPlainString();
                    }
                }
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_BLANK:
                return null;
            case Cell.CELL_TYPE_FORMULA:
                // 处理公式单元格
                return evaluateFormulaCell(cell);
            default:
                return null;
        }
    }

    // 处理公式单元格 - POI 3.14 兼容版本
    private Object evaluateFormulaCell(Cell cell) {
        try {
            // 获取公式计算结果类型
            int resultType = cell.getCachedFormulaResultType();

            switch (resultType) {
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue().trim();
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue();
                    } else {
                        double numericValue = cell.getNumericCellValue();
                        if (numericValue == Math.floor(numericValue)) {
                            return String.valueOf((long) numericValue);
                        } else {
                            BigDecimal bigDecimal = BigDecimal.valueOf(numericValue);
                            return bigDecimal.stripTrailingZeros().toPlainString();
                        }
                    }
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                default:
                    return cell.getCellFormula();
            }
        } catch (Exception e) {
            // 如果获取缓存值失败，直接返回公式字符串
            return cell.getCellFormula();
        }
    }

    @SuppressWarnings("unchecked")
    private List<Map<String, Object>> parseJson(MultipartFile file) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        return mapper.readValue(file.getInputStream(), List.class);
    }
}